﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using Jumpton.Data;
using Jumpton.SFA.Server.BusinessRule;
using System.IO;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Util;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataTable table = GetData("20120813000000", "20120820000000", 26, "CXRS");
            GridView2.DataSource = table;
            GridView2.DataBind();
            GridView1.DataSource = CreateTable(table, "20120813", "20120820");
            GridView1.DataBind();
        }
        
    }
    int total=0;
    int totalCustomer = 0;
    private DataTable GetData(string mintime, string maxtime, int domainid, string code)
    {
        DataTable dt = new DataTable();
        dt = SqlData.OpenSP("SP_PROMOTION_PERSON_REPORT", mintime, maxtime, domainid, code).Tables[0];
        return dt;
    }

    private DataTable CreateTable(DataTable table, string time1, string time2)
    {
        //table = GetData("21021207", "20121210", 26, "CXRS");
        var query0 =
            from temp in table.AsEnumerable().Select(s => s.Field<String>("customername")).Distinct()
            select temp;
        totalCustomer = query0.Count<String>();
        var query1 =
                from temp in table.AsEnumerable().Select(s => s.Field<String>("seller")).Distinct()
                select temp;
        string[] sellerList = null;
        if (query1.Count<String>() > 0)
        {
            sellerList = query1.ToArray<String>();
        }

        //创建临时表
        DataTable dttemp = new DataTable("TempTable");

        DataColumn column1 = new DataColumn("促销员", typeof(string)); //促销员列            
        dttemp.Columns.Add(column1);
        DateTime DateTime1 = Convert.ToDateTime(BusinessLogic.GetDateTime(time1 + "000000", "yyyyMMddHHmmss", "yyyy-MM-dd HH:mm:ss"));
        DateTime DateTime2 = Convert.ToDateTime(BusinessLogic.GetDateTime(time2 + "000000", "yyyyMMddHHmmss", "yyyy-MM-dd HH:mm:ss"));
        //根据时间创建列
        for (DateTime dt = DateTime1; dt < DateTime2.AddDays(1); dt = dt.AddDays(1))
        {
            DataColumn columnSeller = new DataColumn(dt.Month + "." + dt.Day, typeof(string));
            columnSeller.AllowDBNull = true;
            dttemp.Columns.Add(columnSeller);
        }
        DataColumn column2 = new DataColumn("人次小计", typeof(int));
        dttemp.Columns.Add(column2); //合计列
        
        //为每个促销员创建行        
        for (int i = 0; i < sellerList.Length; i++)
        {
            IEnumerable<DataRow> query2 = from temp in table.AsEnumerable()
                                          where temp.Field<string>("seller") == sellerList[i]
                                          select temp;
            //;  s.CopyToDataTable();
            DataRow row = dttemp.NewRow();
            row["促销员"] = sellerList[i];
            for (DateTime dt = DateTime1; dt < DateTime2.AddDays(1); dt = dt.AddDays(1))
            {
                IEnumerable<String> query3 =
                 from temp in query2
                 where Convert.ToDateTime(BusinessLogic.GetDateTime(temp.Field<String>("startime") + "000000", "yyyyMMddHHmmss", "yyyy-MM-dd HH:mm:ss")) == dt
                 select temp.Field<string>("customername");
                //if (query3.ToArray().Length > 0)
                //    row[dt.Month + "." + dt.Day] = query3.ToArray()[0];
                if (query3.ToArray().Length > 0)
                {
                    for (int m = 0; m < query3.ToArray().Length; m++)
                        row[dt.Month + "." + dt.Day] += query3.ToArray()[m] + ",";//一个促销员一天促销的所有店
                     row[dt.Month + "." + dt.Day]= row[dt.Month + "." + dt.Day].ToString().Substring(0, row[dt.Month + "." + dt.Day].ToString().Length - 1);
                }
            }
            row["人次小计"] = query2.Count<DataRow>();
            dttemp.Rows.Add(row);
            total = total + query2.Count<DataRow>();
        }
        return dttemp;
    }

    HSSFWorkbook hssworkbook = new HSSFWorkbook();
    private void ExprotExcel(DataTable dt)
    {        
        DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
        SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
        hssworkbook.DocumentSummaryInformation = dsi;
        hssworkbook.SummaryInformation = si;
        ISheet sheet=hssworkbook .CreateSheet("导出活动点数人次");
        ICellStyle cellStyle = hssworkbook.CreateCellStyle();
        cellStyle.Alignment = HorizontalAlignment.CENTER;
        cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
        cellStyle.BorderTop = cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
        //设置字体
        IFont font = hssworkbook.CreateFont();
        font.FontHeightInPoints = 13;
        cellStyle.SetFont(font);

        IRow rowtitle = sheet.CreateRow(1);
        rowtitle.HeightInPoints = 30;
        ICell celltitle = rowtitle.CreateCell(1);
        celltitle.SetCellValue("导出-活动点数人次（根据手机定位的卖场名称及人工输入的促销员姓名后台自动生成）");
        //IFont font1 = hssworkbook.CreateFont();
        //font1.FontName = "黑体";
        celltitle.CellStyle = cellStyle;
        sheet.AddMergedRegion(new CellRangeAddress(1, 1, 1, dt.Columns.Count));//合并单元格
        
        IRow rowheader = sheet.CreateRow(2);
        for (int k = 0; k < dt.Columns.Count; k++)
        {
            ICell cell = rowheader.CreateCell(k+1);
            cell.SetCellValue(dt.Columns[k].ColumnName);
            cell.CellStyle = cellStyle;
            sheet.SetColumnWidth(k + 1,3700);
        }
        for (int i=0;i<dt.Rows .Count ;i++)
        {
            IRow row = sheet.CreateRow(i+3);
            for (int j = 0; j < dt .Columns .Count; j++)
            {
                ICell cell = row.CreateCell(j+1);
                
                if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString()))
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                cell.CellStyle = cellStyle;
            }
        }

        IRow row2 = sheet.CreateRow(dt.Rows.Count + 3);
        row2.HeightInPoints = 20;
        ICell cell2 = row2.CreateCell(1);
        cell2.SetCellValue("促销员工作人次");
        ICellStyle cellStyle2 = hssworkbook.CreateCellStyle();
        cellStyle2.Alignment = HorizontalAlignment.CENTER;
        cellStyle2.VerticalAlignment = VerticalAlignment.CENTER;
        cellStyle2.FillForegroundColor = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;
        cellStyle2.FillPattern = FillPatternType.SOLID_FOREGROUND;
        cellStyle2.BorderTop = cellStyle2.BorderBottom = cellStyle2.BorderLeft = cellStyle2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
        cell2.CellStyle = cellStyle2;
        sheet.AddMergedRegion(new CellRangeAddress(dt.Rows.Count+3,dt.Rows.Count+3, 1,  dt.Columns.Count-1));//合并单元格

        ICell cell22 = row2.CreateCell(dt.Columns .Count);
        cell22.SetCellValue(total);
        cell22.CellStyle = cellStyle;

        IRow row3 = sheet.CreateRow(dt.Rows.Count + 4);
        row3.HeightInPoints = 25;//设置行高
        ICell cell3 = row3.CreateCell(1);
        cell3.SetCellValue("卖场点数：以每周计数单位，需删除重复项");
        ICellStyle cellStyle3 = hssworkbook.CreateCellStyle();
        cellStyle3.Alignment = HorizontalAlignment.CENTER;
        cellStyle3.VerticalAlignment = VerticalAlignment.CENTER;
        cellStyle3.FillForegroundColor = HSSFColor.GREEN.index;
        cellStyle3.FillPattern = FillPatternType.SOLID_FOREGROUND;
        
        cellStyle3.SetFont(font);
        cellStyle3.BorderTop = cellStyle3.BorderBottom = cellStyle3.BorderLeft = cellStyle3.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
        cell3.CellStyle = cellStyle3;
        sheet.AddMergedRegion(new CellRangeAddress(dt.Rows.Count+4, dt.Rows.Count+4, 1, dt.Columns.Count-1)); //合并

        ICell cell33 = row3.CreateCell(dt.Columns .Count);
        cell33.SetCellValue(totalCustomer);
        cell33.CellStyle = cellStyle;

        //定义输出类型
        HttpContext.Current.Response.Charset = "UTF_8";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
        string filenameDetail = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
        string filename = HttpUtility.UrlEncode(filenameDetail, System.Text.Encoding.UTF8);
        Response.ContentType = "application/vnd.ms-excel";
        Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
        Response.Clear();
        Response.BinaryWrite(WriteToStream().GetBuffer());
        Response.End();
    }

    MemoryStream WriteToStream()
    {
        //Write the stream data of workbook to the root directory
        MemoryStream file = new MemoryStream();
        hssworkbook.Write(file);
        return file;
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        DataTable table = GetData("20120813000000", "20120820000000", 26, "CXRS");
        ExprotExcel(CreateTable(table, "20120813", "20120820"));
    }
}